<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.NamingException" %>
<%@ page import="java.sql.*" %><%--
  Created by IntelliJ IDEA.
  User: WYX
  Date: 2024/5/17
  Time: 上午8:45
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>实验一:数据库连接池</title>
</head>
<body>
<%
    Connection connection = null;
    String id = request.getParameter("id");
    String name = request.getParameter("name");
    String math = request.getParameter("math");
    String english = request.getParameter("english");
    String mess = request.getParameter("submit");
    if(mess==null)
        mess="";
    try {
        Context context = new InitialContext();
        Context envContext = (Context) context.lookup("java:comp/env");
        DataSource dataSource = (DataSource) envContext.lookup("s");
        connection = dataSource.getConnection();
        PreparedStatement preparedStatement = null;
        out.println("数据库连接成功");
        String update = "update s set name = ?,math=?,english=? where id = ?";
        String insert = "insert into s(id,name,math,english) values(?,?,?,?)";
        String delete = "delete from s where id = ?";
        String select = "select * from s where id=?";
        if (mess.contains("更新")) {
            preparedStatement = connection.prepareStatement(update);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, math);
            preparedStatement.setString(3, english);
            preparedStatement.setString(4, id);
            preparedStatement.executeUpdate();
            System.out.println("更新成功");
        } else if (mess.contains("插入")) {
            preparedStatement = connection.prepareStatement(insert);
            preparedStatement.setString(1, id);
            preparedStatement.setString(2, name);
            preparedStatement.setString(3, math);
            preparedStatement.setString(4, english);
            preparedStatement.executeUpdate();
            System.out.println("插入成功");
        } else if (mess.contains("删除")) {
            preparedStatement = connection.prepareStatement(delete);
            preparedStatement.setString(1, id);
            preparedStatement.executeUpdate();
            System.out.println("删除成功");
        } else if (mess.contains("查询")) {
            preparedStatement = connection.prepareStatement(select);
            preparedStatement.setString(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getString("id");
                name = resultSet.getString("name");
                math = resultSet.getString("math");
                english = resultSet.getString("english");
            }
            preparedStatement.executeUpdate();
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        System.out.println("数据库连接关闭");
    }
%>
<form action="" method="post">
    <label>
        输入学号查看或删除:
        <input type="text" name="id"><br>
        <input type="submit" name="submit" value="查询"/>
        <input type="submit" name="submit" value="删除"/>
    </label>
</form>
<form action="" method="post">
    <label>
        更新<br>
        id:<input type="text" name="id" value="<%=id%>"><br>
        姓名:<input type="text" name="name" value="<%=name%>"><br>
        数学分:<input type="text" name="math" value="<%=math%>"><br>
        英语分:<input type="text" name="english" value="<%=english%>"><br>
    </label>
    <input type="submit" name="submit" value="更新"/>
    <input type="submit" name="submit" value="插入"/>
</form>
</body>
</html>
